package DB;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import Gui.Terminal;
import Model.Clothing;
import Model.Product;

/**
 * 
 * @author Allan Malka
 *
 */
public class DBClothing 
{
	//If anything changes in the database, then this should be easy to find and edit:
	private final String productID = "productID";
	private final String name = "name";
	private final String productType = "ProductType";
	private final String purchasePrice = "purchasePrice";
	private final String salesPrice = "salesPrice";
	private final String countryOfOrigin = "countryOfOrigin";
	private final String stock = "stock";
	private final String minStock = "minStock";
	//Clothing
	private final String size = "size";
	private final String colour = "color";
	//DBProduct connection
	private DBProduct dbP;
	
	//String builder
	private final String selectAll = String.format("SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s FROM Product INNER JOIN Clothing ON Product.ProductID = Clothing.CprodID", 
													productID, name, productType, purchasePrice, salesPrice, countryOfOrigin, stock, minStock, size, colour);
	/*private final String createProduct = String.format("INSERT INTO Product %s, %s, %s, %s, %s, %s, %s VALUES ", 
														productID, name, purchasePrice, salesPrice, countryOfOrigin, stock, minStock);*/
	private final String createClothing = String.format("INSERT INTO Clothing %s, %s, %s VALUES ", productID, size, colour);
	private final String deleteClothing = "DELETE FROM Product INNER JOIN Clothing ON Product.ProductID = Clothing.CProdID";
	
	private DBConnection conn;
	
	public DBClothing()
	{
		conn = DBConnection.getInstance();
		dbP = new DBProduct();
	}
	
	public Clothing getClothing(String _prodID)
	{
		Clothing result = null;
		
		//Create statement
		Statement stmt = null;
		try {
			stmt = conn.getDBcon().createStatement();
		}
		catch(SQLException sE) {
			Terminal.say("An error occured during statement creation. \n Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Execute statement
		String condition = WhereAdder(selectAll, productID + " = " +_prodID);
		try {
			stmt.execute(condition);
		}
		catch(SQLException sE) {
			Terminal.say("An error occured executing the statement.");
			Terminal.say(condition);
		    Terminal.say("Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Get result
		ResultSet resSet = null;
		try {
			resSet = stmt.getResultSet();
		}
		catch(SQLException sE) {
			Terminal.say("The resultSet could not be retrieved. \n Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Build the Clothings
		if(resSet != null) {
			result = BuildClothing(resSet);
		}
		else {
			Terminal.say("No result was found");
		}
		
		//Close connection
		if(stmt != null) {
			try {
				stmt.close();
			}
			catch(SQLException sE) {
				Terminal.say("Stack trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
		}
		
		return result;
	}
	
	public ArrayList<Clothing> getAllClothings()
	{
		ArrayList<Clothing> allClothings = new ArrayList<Clothing>();
		
		//Create statement
		Statement stmt = null;
		try {
			stmt = conn.getDBcon().createStatement();
		}
		catch(SQLException sE) {
			Terminal.say("Stack Trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Execute statement
		try {
			stmt.execute(selectAll);
		}
		catch(SQLException sE) {
			Terminal.say("Stack Trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Retrieve results
		ResultSet resSet = null;
		try {
			resSet = stmt.getResultSet();
		}
		catch(SQLException sE) {
			Terminal.say("Stack Trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Build an Clothing object for each result
		try {
			do {
				allClothings.add(BuildClothing(resSet));
			} while(resSet.next());
		}
		catch(SQLException sE) {
			Terminal.say("Stack Trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		//Close connection
		if(stmt != null) {
			try {
				stmt.close();
			}
			catch(SQLException sE) {
				Terminal.say("Stack Trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
		}
		
		return allClothings;
	}
	
	private String WhereAdder(String queryString, String condition)
	{
		return queryString + " WHERE " + condition;
	}
	
	private Clothing BuildClothing(ResultSet resSet)
	{
		try {
			//ResultSet starts at -1, so a first next() call is needed to find the first item.
			if(resSet.next()) {
				Product p = dbP.getProduct(productID);
				
				String prodID = p.getProductID();
				String name = p.getName();
				double pPrice = p.getPurchasePrice();
				double sPrice = p.getSalesPrice();
				String cOO = p.getCountryOfOrigin();
				String prodType = p.getProductType();
				int stock = p.getStock();
				int minStock = p.getMinStock();
				
				String size = resSet.getString(this.size);
				String colour = resSet.getString(this.colour);
				
				return new Clothing(prodID, name, pPrice, sPrice, cOO, prodType, stock, minStock, size, colour);
			}
			else {
				return null;
			}
		}
		catch(SQLException sE) {
			Terminal.say("Could not build Clothing object \n Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
			
			return null;
		}
	}

	public int DeleteClothing(String id)
	{
		int rc = -1;

		Statement stmt = null;
		
		try {
			stmt = conn.getDBcon().createStatement();
			stmt.setQueryTimeout(5);
			
			rc = stmt.executeUpdate(WhereAdder(deleteClothing, id));
			stmt.close();
		}
		catch(SQLException sE) {
			Terminal.say("Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		return(rc);
	}

	public int CreateClothing(Clothing cloth)
	{
		
		int rc = -1;
		String newID = String.format("SELECT MAX(CAST(value AS Int)) FROM %s WHERE VALUE NOT LIKE '%[a-z]%' AND ISNUMERIC(VALUE) = 1", "Product");
		newID += 10;
		/*String insertPQuery = createProduct + newID + ", " + cloth.getName() + ", " + cloth.getPurchasePrice() + ", " 
							+ cloth.getSalesPrice() + ", " + cloth.getCountryOfOrigin() + ", " + cloth.getStock() + ", " + cloth.getMinStock();*/
		
		String insertCQuery = createClothing + newID + ", " + cloth.getSize() + ", " + cloth.getColour();
		
		//Create statement
		Statement stmt = null;
		try {
			stmt = conn.getDBcon().createStatement();
		}
		catch(SQLException sE) {
			Terminal.say("An error occured during statement creation for insert. \n Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		
		//Execute statement
		try {
			if(dbP.InsertProduct(cloth) < 0) {
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(insertCQuery);
			}
			else {
				Terminal.say("Error in insertProduct");
			}
		}
		catch(SQLException sE) {
			Terminal.say("An error occured executing the statement for insert.");
			Terminal.say(createClothing);
		    Terminal.say("Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
		}
		
		
		//Close connection
		if(stmt != null) {
			try {
				stmt.close();
			}
			catch(SQLException sE) {
				Terminal.say("Stack trace: ");
				Terminal.say(sE.getStackTrace().toString());
			}
		}
		return (rc);
	}

	public void UpdateClothing(String _prodID, String _size, String _colour, String _name, double _pPrice, double _sPrice, String _country, String _prodType, int _stock, int _minStock)
	{
		DBProduct prod = new DBProduct();
		Statement stmt = null;
		try {
			stmt = conn.getDBcon().createStatement();
		}
		catch(SQLException sE) {
			Terminal.say("Could not create statement: \n" + sE.getStackTrace());
		}
		String updateQuery = String.format("UPdATE Clothing SET CProdID = '%s', size = '%s', color = '%s' FROM Clothing", 
				_prodID, _size, _colour);
		
		try {
			stmt.executeQuery(WhereAdder(updateQuery, "CProdID = " + _prodID));
			stmt.setQueryTimeout(5);
			prod.updateProduct(_prodID, _name, _pPrice, _sPrice, _country, _prodType, _stock, _minStock);
		}
		catch(SQLException sE) {
			Terminal.say("Error in executing: \n" + updateQuery);
		}

		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("Close Statement error", e);
		}
	}
	
}
